﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CommonsLib.Utility;
using System.Data.SqlClient;
using MaxLeaf.Platform.Functions;
using System.Data.Common;
using MaxLeaf.Platform.Commons;
using MaxLeaf.Platform.DataContract;
using CommonsLib.DB;

namespace MaxLeaf.Platform.DALImplementation
{
    public class poReqQryByItemDAL:BaseDAL
    {
  

        internal static string ItemTableName(object model)
        {
            string strSql = string.Format(@"
                (
                    select 0 as KeyID
                    ,D.ItemID
                    ,D.UnitID
                    ,H.CompanyID as HdrCompanyID
                    ,D.CompanyID
                    ,com.Company_Name as CompanyName
                    ,isnull(g_k.usegoods_name,'') as Keywords
                    ,g.goods_name as GoodsName
                    ,g.spec as spec
                     ,isnull(g_b.brand_name,'') as BrandName
                    ,g.sku_num as SKU
                    ,isnull({1}F_GetItemAttrValue(D.ItemID),'') as GoodsAttr
                    ,isnull(g_u.measure_name,'') as UnitName
                    ,D.WebPrice
                    ,D.Cost
                    ,isnull(loc.onhand,0) as onhand
                    ,isnull(loc.Min,0) as Min
                    ,isnull(loc.Max,0) as Max
       
                    ,sum(d.ReqQty) as ReqQty
                    ,sum(d.Subtotal) as Subtotal
                    ,Count(distinct(d.ItemID)) as GoodsTypeCount

                    from {0}poReqHdr H
                    inner join  {0}poReqDtl D  on h.finterid=d.hdrid
                    inner join {1}ImInvMaster i on d.itemid=i.FinterID
                    left join {2}ImInvloc loc on d.itemid=loc.itemid  and  loc.companyid=D.CompanyID
                    inner join {1}goods_main g on g.goods_id=i.GoodsID 
                    inner join {1}mCompany com on com.CompanyID=D.CompanyID

                    left join {1}goods_UserKeywords g_k on g_k.company_id=D.CompanyID and i.FinterID=g_k.ItemID
                    left join {1}goods_brand g_b on g_b.brand_id=g.brand_id
                    left join {1}goods_attribute g_a on g_a.goods_attr_id=i.GoodsAttrID
                    left join {1}goods_measure_unit g_u on g_u.measure_unit_id=D.UnitID
                    where h.posted=0 and d.posted=0 and h.is_delete=0 and d.is_delete=0
                    group by 
                    D.ItemID
                    ,D.UnitID
                    ,H.CompanyID
                    ,D.CompanyID
                    ,com.Company_Name
                    ,isnull(g_k.usegoods_name,'') 
                    ,g.goods_name  
                    ,g.spec  
                     ,isnull(g_b.brand_name,'')  
                    ,g.sku_num  
                    ,isnull({1}F_GetItemAttrValue(D.ItemID),'')
                    ,isnull(g_u.measure_name,'')  
                    ,D.WebPrice
                    ,D.Cost
                    ,isnull(loc.onhand,0)
                    ,isnull(loc.Min,0)
                    ,isnull(loc.Max,0)
 
                ) a"
                    , GetOrdDatabase()
                    , GetBaseDatabase()
                    , GetInvDatabase()
                    );

            return strSql;
        }

        

        internal static string DepTableName(object model)
        {
            string strSql = string.Format(@"
                (
                    select 0 as KeyID
                    ,D.ItemID
                    ,D.UnitID
                    ,isnull(g_k.usegoods_name,'') as Keywords
                    ,g.goods_name as GoodsName
                    ,g.spec as spec
                     ,isnull(g_b.brand_name,'') as BrandName
                    ,g.sku_num as SKU
                    ,isnull({1}F_GetItemAttrValue(D.ItemID),'') as GoodsAttr
                    ,isnull(g_u.measure_name,'') as UnitName
                    ,D.WebPrice
                    ,D.Cost
                    ,H.CompanyID as HdrCompanyID
                    ,D.CompanyID
                    ,com.Company_Name as CompanyName
                    ,dep.DepID       
                    ,dep.DepName
                    ,isnull(loc.onhand,0) as onhand
                    ,isnull(loc.Min,0) as Min
                    ,isnull(loc.Max,0) as Max
                    
                    ,sum(d.ReqQty) as ReqQty
                    ,sum(d.Subtotal) as Subtotal
                    ,Count(distinct(d.ItemID)) as GoodsTypeCount

                    from {0}poReqHdr H
                    inner join  {0}poReqDtl D  on h.finterid=d.hdrid
                    inner join {1}ImInvMaster i on d.itemid=i.FinterID
                    left join {2}ImInvloc loc on d.itemid=loc.itemid  and  loc.companyid=D.CompanyID
                    inner join {1}goods_main g on g.goods_id=i.GoodsID
                    inner join {1}mDepartment dep on dep.depid=D.DepID
                    inner join {1}mCompany com on com.CompanyID=D.CompanyID

                    left join {1}goods_UserKeywords g_k on g_k.company_id=D.CompanyID and i.FinterID=g_k.ItemID
                    left join {1}goods_brand g_b on g_b.brand_id=g.brand_id
                    left join {1}goods_attribute g_a on g_a.goods_attr_id=i.GoodsAttrID
                    left join {1}goods_measure_unit g_u on g_u.measure_unit_id=D.UnitID
                    where h.posted=0 and d.posted=0 and h.is_delete=0 and d.is_delete=0
                    group by 
                    
                    D.ItemID
                    ,D.UnitID
                    ,isnull(g_k.usegoods_name,'') 
                    ,g.goods_name  
                    ,g.spec  
                     ,isnull(g_b.brand_name,'')  
                    ,g.sku_num  
                    ,isnull({1}F_GetItemAttrValue(D.ItemID),'')
                    ,isnull(g_u.measure_name,'')  
                    ,D.WebPrice
                    ,D.Cost
                    ,H.CompanyID 
                    ,D.CompanyID
                    ,com.Company_Name
                    ,dep.DepID       
                    ,dep.DepName 
                    ,isnull(loc.onhand,0)
                    ,isnull(loc.Min,0)
                    ,isnull(loc.Max,0)
                ) a"
                    , GetOrdDatabase()
                    , GetBaseDatabase()
                    , GetInvDatabase()
                    );

            return strSql;
        }

        internal static string OrdTableName(object model)
        {
            string strSql = string.Format(@"
                (
                    select 0 as KeyID
                    ,D.ItemID
                    ,D.UnitID
                    ,isnull(g_k.usegoods_name,'') as Keywords
                    ,g.goods_name as GoodsName
                    ,g.spec as spec
                     ,isnull(g_b.brand_name,'') as BrandName
                    ,g.sku_num as SKU
                    ,isnull({1}F_GetItemAttrValue(D.ItemID),'') as GoodsAttr
                    ,isnull(g_u.measure_name,'') as UnitName
                    ,D.WebPrice
                    ,D.Cost
                    ,isnull(loc.onhand,0) as onhand
                    ,isnull(loc.Min,0) as Min
                    ,isnull(loc.Max,0) as Max
                    ,H.CompanyID as HdrCompanyID
                    ,D.CompanyID
                    ,com.Company_Name as CompanyName
                    ,dep.DepID       
                    ,dep.DepName
                    ,H.FInterID
                    ,sum(d.ReqQty) as ReqQty
                    ,sum(d.Subtotal) as Subtotal
                    ,Count(distinct(d.ItemID)) as GoodsTypeCount
                    ,StoresReqHdr.OrdNo
                    ,StoresReqHdr.DocType
                    ,StoresReqHdr.DocDate
                    ,StoresReqHdr.ReqUserID

                    from {0}poReqHdr H
                    inner join  {0}poReqDtl D  on h.finterid=d.hdrid
                    inner join  {0}imStoresReqDtl StoresReqDtl  on StoresReqDtl.dtlid=D.StoreReqDtlID
                    inner join  {0}imStoresReqHdr StoresReqHdr  on StoresReqDtl.hdrid=StoresReqHdr.finterid
                    inner join {1}ImInvMaster i on d.itemid=i.FinterID
                    left join {2}ImInvloc loc on d.itemid=loc.itemid  and  loc.companyid=D.CompanyID
                    inner join {1}goods_main g on g.goods_id=i.GoodsID
                    inner join {1}mDepartment dep on dep.depid=D.DepID
                    inner join {1}mCompany com on com.CompanyID=D.CompanyID
                    
                    left join {1}goods_UserKeywords g_k on g_k.company_id=D.CompanyID and i.FinterID=g_k.ItemID
                    left join {1}goods_brand g_b on g_b.brand_id=g.brand_id
                    left join {1}goods_attribute g_a on g_a.goods_attr_id=i.GoodsAttrID
                    left join {1}goods_measure_unit g_u on g_u.measure_unit_id=D.UnitID
                    where h.posted=0 and d.posted=0 and h.is_delete=0 and d.is_delete=0
                    group by 
                    
                    D.ItemID
                    ,D.UnitID
                    ,isnull(g_k.usegoods_name,'') 
                    ,g.goods_name  
                    ,g.spec  
                     ,isnull(g_b.brand_name,'')  
                    ,g.sku_num  
                    ,isnull({1}F_GetItemAttrValue(D.ItemID),'')
                    ,isnull(g_u.measure_name,'')  
                    ,D.WebPrice
                    ,D.Cost
                    ,isnull(loc.onhand,0)
                    ,isnull(loc.Min,0)
                    ,isnull(loc.Max,0)
                    ,H.CompanyID
                    ,D.CompanyID
                    ,com.Company_Name
                    ,dep.DepID       
                    ,dep.DepName 
                    ,H.FInterID
                    ,StoresReqHdr.OrdNo
                    ,StoresReqHdr.DocType
                    ,StoresReqHdr.DocDate
                    ,StoresReqHdr.ReqUserID
                ) a"
                    , GetOrdDatabase()
                    , GetBaseDatabase()
                    , GetInvDatabase()
                    );

            return strSql;
        }

        
    }
}
